At 21:04 +0300 on 29/04/1999, Brett W. McCoy wrote:
> I think, Aaron, you could get a count of distinct customer names like this:
>
> SELECT DISTINCT customer_username, COUNT(*) FROM customerdata
> GROUP BY customer_username;
>
> This will give you 2 columns, one with the distinct customer_usernames
> and the second with the count of each. The GROUP BY caluse is important
> here. This looks like what you wanted in your original query.
No, Brett. COUNT( DISTINCT ...) is supposed to count the number of distinct
names in a table. Here, I created a test table:
testing=> select * from test;
customer
--------
moshe
david
hanna
david
sarah
moshe
suzanne
moshe
moshe
(9 rows)
The distinct names are:
testing=> select distinct customer
testing-> from test;
customer
--------
david
hanna
moshe
sarah
suzanne
(5 rows)
So clearly, the datum he wanted was "5" - there are five distinct customers
here.
Your query, however, gives the following:
testing=> select distinct customer, count(*)
testing-> from test
testing-> group by customer;
customer|count
--------+-----
david | 2
hanna | 1
moshe | 4
sarah | 1
suzanne | 1
(5 rows)
Which shows him the number of REPETITIONS on each distinct name.
My ugly query gives:
testing=> select count(*)
testing-> from test t1
testing-> where int( oid ) = (
testing-> SELECT min( int( t2.oid ) )
testing-> FROM test t2
testing-> WHERE t2.customer = t1.customer
testing-> );
count
-----
5
(1 row)
And this is the exact number of distinct names in the table.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
